﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Configuration;


namespace HRAssistant
{
    public partial class AttendenceForm : Form
    {
        
        public AttendenceForm()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connStr= ConfigurationManager.AppSettings["MySqlConnStr"];//;MySQL 数据库连接字符串;
             string sqlCommand="";
             if (textBox1.Text.Trim() != "")
             {
              
                 //根据用户id来查找符合时间条件的记录
                 sqlCommand = " SELECT a.employee_id, CONCAT(  '', b.emp_lastname, b.emp_middle_name, b.emp_firstname ) name, punch_in_user_time, punch_out_user_time, TIMESTAMPDIFF(MINUTE,punch_in_user_time,punch_out_user_time) worktime,state from ohrm_attendance_record a LEFT JOIN hs_hr_employee b ON a.employee_id = b.emp_number  where a.employee_id =" + Convert.ToInt32(textBox1.Text.Trim()) + " and punch_in_user_time between  '" + dateTimePicker1.Value + "'  and   '" + dateTimePicker2.Value + "'  ";
                
             }
             else
             {                                                                                                                                                                 
                 //查找符合时间条件的全部记录
                 sqlCommand = " SELECT a.employee_id, CONCAT(  '', b.emp_lastname, b.emp_middle_name, b.emp_firstname ) name, punch_in_user_time, punch_out_user_time, TIMESTAMPDIFF( MINUTE,punch_in_user_time,punch_out_user_time) worktime,state from ohrm_attendance_record a  LEFT JOIN hs_hr_employee b ON a.employee_id = b.emp_number where  punch_in_user_time between  '" + dateTimePicker1.Value + "'  and   '" + dateTimePicker2.Value + "' order by employee_id ";
             }
            
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                throw;
            }

            try
            {             
                MySqlDataAdapter myadp = new MySqlDataAdapter(sqlCommand, conn);
                DataSet ds = new DataSet();
                myadp.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
            }
            catch(Exception err)
            {
                MessageBox.Show("查询出错，错误信息："+err.Message);
                return;
            }
            finally
            {
                conn.Close();
            } 
            //if (textBox1.Text.Trim() == string.Empty)
            //{
            //    dataGridView1.DataSource = ExecuteSP("sp_work_hour_all", true).Tables[0];  //查询全部员工的工作时间，时间单位为分钟
            //}
            //else
            //{
            //    dataGridView1.DataSource = ExecuteSP("sp_work_hour", false).Tables[0];//查询指定员工的工作时间，时间单位为分钟
            //}
            MessageBox.Show("本次查询完毕!");
        }
		
		public DataSet ExecuteSP(string sp_name,bool isAll)
        {
			MySqlConnection mysqlcon;
            MySqlDataAdapter mysqldata = new MySqlDataAdapter();
            DataSet dataset = new DataSet();
            mysqlcon = new MySqlConnection("server=localhost; user id=root; password=root; database=orangehrm_mysql; pooling=false;");
            mysqldata.SelectCommand = new MySqlCommand();
            mysqldata.SelectCommand.Connection = mysqlcon;
            mysqldata.SelectCommand.CommandText = sp_name;
            mysqldata.SelectCommand.CommandType = CommandType.StoredProcedure;
            
			//设置参数，添加到数据库  
            MySqlParameter startdate_parameter = new MySqlParameter("?startdate", MySqlDbType.DateTime);//mysql的存储过程参数是以?打头的！！！！  
            startdate_parameter.Value =dateTimePicker1.Value;
            mysqldata.SelectCommand.Parameters.Add(startdate_parameter);
            //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++  
            MySqlParameter enddate_parameter = new MySqlParameter("?enddate", MySqlDbType.DateTime);//mysql的存储过程参数是以?打头的！！！！  
            enddate_parameter.Value = dateTimePicker2.Value;
            mysqldata.SelectCommand.Parameters.Add(enddate_parameter);
            //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++  
            if(!isAll)
            {
			MySqlParameter eid_parameter = new MySqlParameter("?eid", MySqlDbType.Int16);//mysql的存储过程参数是以?打头的！！！！  
            eid_parameter.Value =Convert.ToInt32(textBox1.Text.Trim());
            mysqldata.SelectCommand.Parameters.Add(eid_parameter);
			}
            
            try
            {
                mysqldata.Fill(dataset, sp_name);
                return dataset;
            
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                mysqlcon.Close();
            }
  
        }





    }
}
